﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace TireBias.Models
{
   public class KANBANPlanModel
   {
      public class KANBANPlan_DS
      {
   //BiasPlan
         public string _Pbiasno { get; set; }
         public string _Pplandt { get; set; }
         public string _Pitemno { get; set; }
         public string _topcrdcd { get; set; }
         public string _sizes { get; set; }
         public string _forming { get; set; }
         public string _Text { get; set; }
         public string _plansts { get; set; }
         public string _Uptemp { get; set; }
         public string _Uptdat { get; set; }
         public string _actflag { get; set; }
         public string _actdt { get; set; }

         public string _empname { get; set; }
         public string _shift { get; set; }
         public string _finflag { get; set; }
         public string _findat { get; set; }
         public string _Tcrdlot { get; set; }
         public string _toproll { get; set; }
         public string _topexp { get; set; }
         public string _toplen { get; set; }
         public string _topauthemp { get; set; }
         public string _topreatxt { get; set; }
         public string _topshtxt { get; set; }
         public string _pP1 { get; set; }
         public string _pP2 { get; set; }
         public string _pP3 { get; set; }
         public string _pP4 { get; set; }
//Kanban
         public string _biasno { get; set; }
         public string _plandt { get; set; }
         public string _itemno { get; set; }
         public string _subItem { get; set; }
         public string _P1 { get; set; }
         public string _P2 { get; set; }
         public string _P3 { get; set; }
         public string _P4 { get; set; }
         public string  _TmpP1 { get; set; }
         public string  _TmpP2 { get; set; }
         public string _fileSTDModifyBy { get; set; }
         public string _fileSTDModifyDT { get; set; }
         public string _STDvalid { get; set; }
         public string _fileINFModifyBy { get; set; }
         public string _fileINFModifyDT { get; set; }
         public string _INFvalid { get; set; }
         public string _Angel { get; set; }
         public string _angPly1 { get; set; }
         public string _Information         {
             get;
             set;
         }

      }

      public KANBANPlan_DS GET_BIASNO(string code)
      {
        
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * ");
         str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASNO t");

         str.AppendLine(" where biasbc=@code");


         SqlCommand cmd = new SqlCommand(str.ToString() , cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;



         cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = code;

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         KANBANPlan_DS list = new KANBANPlan_DS();
         if (ds.Tables[0].Rows.Count > 0)
         {
            DataRow items = ds.Tables[0].Rows[0];
            list._biasno = items["biasno"].ToString();
            list._itemno = items["biasopt"].ToString();
         }
         cmd.Connection.Close();
         return list; 
      }

      public List<KANBANPlan_DS> SEL_KANBANPLAN(string biasno,string dtpln="",string itemno="",Boolean finish=true)
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select  p.* ,k.biasno kBiasno,k.plandt,k.itemno,k.P1 p_1,k.P2 p_2,k.P3 p_3,k.P4 p_4 ");
         str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN p");
         if (finish)
         {
             str.AppendLine(" INNER JOIN ");
         }else    str.AppendLine(" LEFT JOIN ");
        str.AppendLine(  Nemo.GetINI("Dbase", "Base") + ".dbo.KANBANPLAN k on p.biasno=k.biasno ");
         str.AppendLine(" and p.plandt=k.plandt and p.itemno=k.itemno ");

         str.AppendLine(" where plansts='L'  and p.biasno=@biasno");
         if (finish)
         {
             str.AppendLine(" and isnull(finflag,'') = '' ");
         }

         if (StrUtil.IsNotEmpty(dtpln))
         {
            str.AppendLine(" and p.plandt=convert(datetime,@plndt,120) ");
         }

         if (StrUtil.IsNotEmpty(itemno))
         {
            str.AppendLine(" and p.itemno=@itemno ");
         }

         SqlCommand cmd = new SqlCommand(str.ToString() + " order by k.plandt,k.itemno ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(dtpln))
         {
            cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
         }

         if (StrUtil.IsNotEmpty(itemno))
         {
            cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;
            
         }
        
         cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<KANBANPlan_DS> list = new List<KANBANPlan_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            KANBANPlan_DS model = new KANBANPlan_DS();
            model._Pbiasno = items["biasno"].ToString();
            model._Pitemno = items["itemno"].ToString();
            model._topcrdcd = items["topcrdcd"].ToString();
            model._sizes = items["sizes"].ToString();
            model._forming = items["Forming"].ToString();            
            model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._pP1 = items["P1"].ToString();
            model._pP2 = items["P2"].ToString();
            model._pP3 = items["P3"].ToString();
            model._pP4 = items["P4"].ToString();
            model._P1 = items["P_1"].ToString();
            model._P2 = items["P_2"].ToString();
            model._P3 = items["P_3"].ToString();
            model._P4 = items["P_4"].ToString();
            model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
            model._Uptemp = items["Uptemp"].ToString();
            model._Text = items["text"].ToString();

            model._empname = items["empname"].ToString();
            model._shift = items["shift"].ToString();
            model._finflag = items["finflag"].ToString();
            model._findat = DateUtil.ToDisplayDateTime(items["findat"], DateUtil.eLanguage.EN);
            model._Tcrdlot = items["tcrdlot"].ToString();
            model._toproll = items["toproll"].ToString();
            model._topexp = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
            model._toplen = items["toplen"].ToString();
            model._topauthemp = items["topauthemp"].ToString();
            model._topreatxt = items["topreatxt"].ToString();
            model._topshtxt = items["topshtxt"].ToString();

         

            list.Add(model);
         }
         return list;
      }

      public Boolean UPDATE_STATUS_BIAS(ConnectionWrapper cnn, TireBias.Models.BIASSUBPlanModel.BIASSUBPlan_DS data)
      {
          try
          {
              StringBuilder comstr = new StringBuilder();
              comstr.AppendLine(" UPDATE ");
              comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN]");
              comstr.AppendLine(" SET findat=getdate(),finflag='X' ,actflag='X',actdt=getdate()  ");
              comstr.AppendLine(", NFINEmp=@nofinemp,NFINshtxt=@nofinshtxt");
              comstr.AppendLine(" where plandt=convert(datetime,@plndt,120) and biasno=@biasno ");
              comstr.AppendLine(" and itemno=@itemno ");

              CommandWrapper cmd = cnn.CreateCommand();
              cmd.CommandText = comstr.ToString();
              cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(data._plandt, WinNemoLib.DateUtil.eLanguage.EN));
              cmd.AddParameterStringWithValue("@biasno", data._biasno);
              cmd.AddParameterStringWithValue("@sitemno", data._sitemno);
              cmd.AddParameterStringWithValue("@itemno", data._itemno);
              cmd.AddParameterStringWithValue("@nofinemp", data._sauthemp);
              cmd.AddParameterStringWithValue("@nofinshtxt", data._sshtxt);

              if (cmd.ExecuteNonQuery() <= 0)
              {
                  return false;
              }

              return true;
          }
          catch (Exception ex)
          {
              return false;
              throw ex;
          }

      }

      public Boolean UPD_KANBANPLAN(KANBANPlan_DS data)
      {
         try
         {
            using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();

               StringBuilder comstr = new StringBuilder();
               comstr.AppendLine("UPDATE " + Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN]");
                comstr.AppendLine("SET ");
                comstr.AppendLine(" [empname]=@empname, [shift]=" + Nemo.GetINI("Dbase", "Base") + ".[dbo].[GET_SHIFT_PRD](getdate()) ");
                comstr.AppendLine(",[Tcrdlot]=@Tcrdlot,");
                comstr.AppendLine("[toproll]=@toproll,");
                comstr.AppendLine("[topexp]=convert(datetime,@topexp,120) ,");
                comstr.AppendLine("[toplen]=@toplen,");
                comstr.AppendLine("[topauthemp]=@topauthemp,");
                comstr.AppendLine("[topreatxt]=@topreatxt,[topshtxt]=@topshtxt");
                comstr.AppendLine("where biasno=@biasno and plandt = convert(datetime,@plandt,120) and itemno=@itemno");
               CommandWrapper cmd = cnn.CreateCommand();
               cmd.CommandText = comstr.ToString();

               cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(data._plandt, WinNemoLib.DateUtil.eLanguage.EN));
               cmd.AddParameterStringWithValue("@topexp",  DateUtil.ToInternalDate(data._topexp, WinNemoLib.DateUtil.eLanguage.EN));
               cmd.AddParameterStringWithValue("@Tcrdlot", data._Tcrdlot);
               cmd.AddParameterStringWithValue("@toproll", data._toproll);
               cmd.AddParameterStringWithValue("@toplen", data._toplen);
               cmd.AddParameterStringWithValue("@topauthemp", data._topauthemp);
               cmd.AddParameterStringWithValue("@topreatxt", data._topreatxt);
               cmd.AddParameterStringWithValue("@topshtxt", data._topshtxt);
               cmd.AddParameterStringWithValue("@biasno", data._biasno);
               cmd.AddParameterStringWithValue("@itemno", data._itemno);
               cmd.AddParameterStringWithValue("@empname", data._empname);
            

               cmd.ExecuteNonQuery();


               cnn.CommitTransaction();
               return true;
            }
         }
         catch (Exception)
         {

            return false;
         }

      }

      public Boolean SKIP_STATUS(string SEQ, string autEmp, string txtshot,string biasno)
      {
          string[] seqs = SEQ.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
          try
          {
              using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
              {
                  cnn.Open();
                  cnn.BeginTransaction();

                  StringBuilder comstr = new StringBuilder();
                  comstr.AppendLine(" UPDATE ");
                  comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASPLAN] SET");
                  comstr.AppendLine(" Skpflag='X' , skpdt=getdate(),skpemp=@autEmp,skpshtxt=@skpshtxt ");
                  comstr.AppendLine(" ,finflag='X' , Findat=getdate(),actflag='X' , actdt=getdate()  ");
                  comstr.AppendLine(" where  plandt = convert(datetime,@plndt,120) and biasno="+biasno);
                  comstr.AppendLine(" and  convert(float,itemno) = convert(float," + seqs[1] + ")");

                  CommandWrapper cmd = cnn.CreateCommand();
                  cmd.CommandText = comstr.ToString();


                  cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(seqs[0], WinNemoLib.DateUtil.eLanguage.EN));
                  cmd.AddParameterStringWithValue("@autEmp", autEmp);
                  cmd.AddParameterStringWithValue("@skpshtxt", txtshot);

                 
                  if ( cmd.ExecuteNonQuery() <= 0)
                  {
                     cnn.RollbackTransaction();
                     return false;
                  }

                  cnn.CommitTransaction();
                  return true;
              }
          }
          catch (Exception)
          {

              return false;
          }

      }


      public List<KANBANPlan_DS> GET_LAST_FINISH(string biasno)
      {
          
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN t");
          str.AppendLine(" where finflag='X' and skpflag is null  ");
          str.AppendLine(" and biasno= " + biasno);

          SqlCommand cmd = new SqlCommand(str.ToString() + " order by plandt desc,convert(int,itemno) desc ", cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;


      

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<KANBANPlan_DS> list = new List<KANBANPlan_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return list;
          }
          for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
          {
              DataRow items = ds.Tables[0].Rows[cuIndex];
              KANBANPlan_DS model = new KANBANPlan_DS();
              model._biasno = items["biasno"].ToString();
              model._itemno = items["itemno"].ToString();
              model._topcrdcd = items["topcrdcd"].ToString();
              model._sizes = items["sizes"].ToString();
              model._forming = items["Forming"].ToString();
              model._plansts = items["Plansts"].ToString();
              model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
              model._P1 = items["P1"].ToString();
              model._P2 = items["P2"].ToString();
              model._P3 = items["P3"].ToString();
              model._P4 = items["P4"].ToString();
              model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
              model._Uptemp = items["Uptemp"].ToString();
              model._Text = items["text"].ToString();
              model._toproll = items["toproll"].ToString();
              model._Tcrdlot = items["tcrdlot"].ToString();
              model._toplen = items["toplen"].ToString();
              model._topexp = DateUtil.ToDisplayDate(items["topexp"], DateUtil.eLanguage.EN);
              model._actflag = items["actflag"].ToString();
              model._finflag = items["finflag"].ToString();
              model._shift = items["shift"].ToString();
             

              list.Add(model);
          }
          return list;
      }

   }
}